﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using System.ComponentModel.Composition;
using System.Data;
using com.nm;
using com.nm.Helper;
using com.nm.library.Excels;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using System.Diagnostics;

namespace DistributeAPI.Export
{
    /// <summary>
    /// 企划订单量计算Excel导入
    /// </summary>
    [Export(typeof(ExcelImport))]
    public class PlanningOrderQuantityExcel : ExcelImport
    {
        private static int num = 0;
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        public override ExcelImportType Type => ExcelImportType.PLANNINGORDERQUANTITY;

        public override Dictionary<string, ImportVerify> DictFields => dictFields;

        /// <summary>
        /// Excel字段映射
        /// </summary>
        private static readonly Dictionary<string, ImportVerify> dictFields = new List<ImportVerify> {
        new ImportVerify{ ColumnName="店铺编号",    FieldName="ShopCode" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="店铺名称",    FieldName="ShopName" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,false,false)},
            new ImportVerify{ ColumnName="货号",    FieldName="MaterialID" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="尺码长度",    FieldName="SizeLength" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="数量",    FieldName="TotalQty" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,10,true,false)},
           }.ToDictionary(e => e.ColumnName, e => e);

        /// <summary>
        /// 城市码比资料缓存
        /// </summary>
        public IList<PlanningOrderQuantityModelSizeRate> listSizeRateCache = null;

        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, int userId)
        {
            int errorIndex = 0;
            var index = 0;
            var existRowCount = 0;
            var result = new ImportResult();
            if (dt.Rows.Count == 0)
            {
                result.IsSuccess = false;
                result.Message = "Excel中没有检测到数据";
                return result;
            }

            #region 构造一个新的Datatable dtError

            //构造一个新的Datatable dtError
            DataTable dtError = new DataTable();
            dtError.Columns.Add("CompanyID", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopID", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopCode", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopName", System.Type.GetType("System.String"));
            dtError.Columns.Add("MaterialID", System.Type.GetType("System.String"));
            dtError.Columns.Add("TotalQty", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeLength", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeMinQty", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("Qty", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("Flag", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("MofifyDTM", System.Type.GetType("System.DateTime"));

            #endregion 构造一个新的Datatable dtError
            //删除语句集合
            HashSet<string> deleteHashSet = new HashSet<string>();

            #region Cache

            DataTable dtSelectCache = null;
            var strWhere = GetTupleFromDataTable(dt, "ShopCode", "MaterialID", "SizeLength");
            var listSqlCache = $@"SELECT stt.SizeID, stt.SizeName, m.minqty, m.modelid, m.SizeTypeFlag, m.SizeLength, ms.SizeCodeSort, s.shopcode, c.MaterialID, m.SizeLength from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID and c.CompanyID = 'KM' left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where  s.shopcode in ({strWhere.Item1}) and c.MaterialID in ({strWhere.Item2}) and m.SizeLength in ({strWhere.Item3})";
            dtSelectCache = Helper.ExecuteDataTable(new VerticaCommand(listSqlCache, null));
            var listSelectCache = ModelConvertHelper<MinDeepCacheModel>.ConvertToModel(dtSelectCache);

            string strSqlShop = $@"SELECT shopid,shopname,shopcode from dw.dw_Shop where ShopCode in({strWhere.Item1})";
            var dtShop = Helper.ExecuteDataTable(new VerticaCommand(strSqlShop, null));

            #region 查询同城的尺码占比
            string strSql = $"SELECT SizeID,SizeRate,ModelID,SizeTypeFlag,ShopCode from goods.goods_DC_Bas_CitySizeRate  r left join dw.dw_Shop s on r.AreaID = s.AreaID WHERE SizeRate > 0 and s.ShopCode in({strWhere.Item1})";
            var cmd = Helper.GetSqlStringCommand(strSql);
            var dtCity = Helper.ExecuteDataTable(cmd);
            listSizeRateCache = ModelConvertHelper<PlanningOrderQuantityModelSizeRate>.ConvertToModel(dtCity);
            #endregion 查询同城的尺码占比
            #endregion Cache

            try
            {
                //先遍历所有的数据.
                //拼接删除语句放到一个list
                var cmdList = new List<CommandInfo>();
                PlanningOrderQuantityModelSizeList model = new PlanningOrderQuantityModelSizeList();
                #region 构造一个新的Datatable dtNew

                //构造一个新的Datatable dtNew
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add("CompanyID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("ShopID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("ShopCode", System.Type.GetType("System.String"));
                dtNew.Columns.Add("MaterialID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("TotalQty", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeLength", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeMinQty", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("Qty", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("MofifyDTM", System.Type.GetType("System.DateTime"));
                dtNew.Columns.Add("Checker", System.Type.GetType("System.String"));

                #endregion 构造一个新的Datatable dtNew
                int count = 0;
                int limit = 1000;


                //time.Start();
                foreach (DataRow item in dt.Rows)
                {

                    errorIndex = dt.Rows.IndexOf(item) + 7;
                    model.ShopCode = item["ShopCode"].ToString().Trim();
                    model.MaterialID = item["MaterialID"].ToString().Trim();

                    int TotalQty = 0;
                    if (item["TotalQty"] != DBNull.Value)
                    {
                        int temp = 0;
                        Int32.TryParse(item["TotalQty"]?.ToString().Trim(), out temp);
                        TotalQty = temp;
                    }
                    model.TotalQty = TotalQty;
                    //如果skc总量小于等于0,则跳过
                    if (model.TotalQty <= 0)
                    {
                        InsertToError(dtError, model);
                        index++; continue;
                    }

                    if (item["SizeLength"] != DBNull.Value)
                    {
                        model.SizeLength = item["SizeLength"].ToString().Trim();
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        index++; continue;
                    }

                    //根据shopcode和materialid获取最低深度尺码列表
                    if (model.ShopCode.Length > 0 &&
                        model.MaterialID.Length > 0)
                    {
                        //timeDeep.Start();
                        //检测是否同一店铺,同一货号有多条记录,只保留最后的一条
                        var rowExist = dtNew.Select($"ShopCode ='{model.ShopCode}' and MaterialID = '{model.MaterialID}' ");
                        if (rowExist != null && rowExist.Count() > 0)
                        {
                            foreach (var r in rowExist)
                            {
                                dtNew.Rows.Remove(r);
                            }
                            existRowCount++;
                        }

                        #region 检测店铺是否存在 
                        //缓存到一个 list里边,在循环外层只查询一次sql
                        //string strSqlShop = $@"SELECT shopid,shopname from dw.dw_Shop where ShopCode='{model.ShopCode}'";
                        //检测是否同一店铺,同一货号有多条记录,只保留最后的一条
                        var rowShopExist = dtShop.Select($"ShopCode ='{model.ShopCode}'");
                        if (rowShopExist != null && rowShopExist.Count() > 0)
                        {
                            model.ShopID = rowShopExist[0]["ShopId"]?.ToString();
                            model.ShopName = rowShopExist[0]["ShopName"]?.ToString();
                        }
                        else
                        {
                            InsertToError(dtError, model);
                            index++; continue;
                        }

                        #endregion 检测店铺是否存在

                        #region 获取最低深度尺码列表

                        #region 查找店铺的最低深度
                        DataTable dtSelect = null;
                        //var listSql = $@"SELECT stt.SizeID, stt.SizeName, m.minqty, m.modelid, m.SizeTypeFlag, m.SizeLength, ms.SizeCodeSort from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID  and stt.SizeID = m.SizeID  and c.KindID = m.KindID  and c.ModelID = m.ModelID  and c.ItemID = m.ItemID  and stt.SizeTypeFlag = m.SizeTypeFlag and m.SizeLength = '{model.SizeLength}' left join dw.dw_Shop s on  m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on  stt.SizeID = ms.sizeid where  c.CompanyID = 'KM'  and c.MaterialID = '{model.MaterialID}'  and s.shopcode = '{model.ShopCode}' order by m.SizeLength,ms.SizeCodeSort";
                        //dtSelect = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
                        List<PlanningOrderQuantityModelSize> listSelect = new List<PlanningOrderQuantityModelSize>();
                        List<MinDeepCacheModel> listDeep = new List<MinDeepCacheModel>();
                        if (listSelectCache.Count > 0 && listSelectCache.Any(
                            p => p.ShopCode == model.ShopCode &&
                                     p.MaterialID == model.MaterialID &&
                                     p.SizeLength == model.SizeLength))
                        {
                            listDeep = listSelectCache.Where(
                            p => p.ShopCode == model.ShopCode &&
                                     p.MaterialID == model.MaterialID &&
                                     p.SizeLength == model.SizeLength).Distinct().ToList();
                            listSelect = ConvertList(listDeep);
                        }
                        #endregion 查找店铺的最低深度

                        #region 取本市其他店铺的本货号最低深度
                        //如果找不到,就取本市其他店铺的本货号最低深度
                        if (listSelect.Count == 0)
                        {
                            var listSqlCity = $@"select s.shopid, s.shopname, stt.SizeID, stt.SizeName, ms.SizeCodeSort, m.SizeTypeFlag, m.SizeLength, m.modelid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag and m.SizeLength = '{model.SizeLength}' left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.MaterialID = '{model.MaterialID}' and c.CompanyID = 'KM' and s.shopid =( SELECT s.shopid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.CompanyID = 'KM' and c.MaterialID = '{model.MaterialID}' and s.city_name =( select city_name from dw.dw_Shop where shopcode = '{model.ShopCode}' ) and m.MinQty is not null LIMIT 1 ) order by m.SizeLength, ms.SizeCodeSort";
                            dtSelect = Helper.ExecuteDataTable(new VerticaCommand(listSqlCity, null));
                            listSelect = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtSelect).ToList();
                        }
                        #endregion 取本市其他店铺的本货号最低深度

                        #region 取本省其他店铺的本货号最低深度
                        //如果找不到,就取本省其他店铺的本货号最低深度
                        if (listSelect.Count == 0)
                        {
                            var listSqlProvince = $@"select s.shopid, s.shopname, stt.SizeID, stt.SizeName, ms.SizeCodeSort, m.SizeTypeFlag, m.SizeLength, m.modelid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag and m.SizeLength = '{model.SizeLength}' left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.MaterialID = '{model.MaterialID}' and c.CompanyID = 'KM' and s.shopid =( SELECT s.shopid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.CompanyID = 'KM' and c.MaterialID = '{model.MaterialID}' and s.province_name =( select province_name from dw.dw_Shop where shopcode = '{model.ShopCode}' ) and m.MinQty is not null LIMIT 1 ) order by m.SizeLength,ms.sizecodesort";
                            dtSelect = Helper.ExecuteDataTable(new VerticaCommand(listSqlProvince, null));
                            listSelect = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtSelect).ToList();
                        }
                        #endregion 取本省其他店铺的本货号最低深度

                        #region 取本店铺所在商品区域其他店铺的本货号最低深度
                        //如果找不到,就取本店铺所在商品区域其他店铺的本货号最低深度
                        if (listSelect.Count == 0)
                        {
                            var listSqlBusinessArea = $@"select s.shopid, s.shopname, stt.SizeID, stt.SizeName, ms.SizeCodeSort, m.SizeTypeFlag, m.SizeLength, m.modelid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag and m.SizeLength = '{model.SizeLength}' left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.MaterialID = '{model.MaterialID}'  and c.CompanyID = 'KM' and s.shopid =( SELECT s.shopid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid left join dw.dw_AreaProCity apc on s.AreaID = apc.AreaID and s.CompanyID = apc.CompanyID where c.CompanyID = 'KM' and c.MaterialID = '{model.MaterialID}' and apc.ParentAreaID = (SELECT a.ParentAreaID from dw.dw_Shop s left join  dw.dw_AreaProCity a on s.AreaID = a.AreaID WHERE s.ShopCode='{model.ShopCode}') and m.MinQty is not null LIMIT 1 ) order by m.SizeLength,ms.sizecodesort";
                            dtSelect = Helper.ExecuteDataTable(new VerticaCommand(listSqlBusinessArea, null));
                            listSelect = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtSelect).ToList();
                        }
                        #endregion 取本店铺所在商品区域其他店铺的本货号最低深度

                        #region 取全国其他店铺的本货号最低深度
                        //如果找不到,就取全国其他店铺的本货号最低深度
                        if (listSelect.Count == 0)
                        {
                            var listSqlCountry = $@"select s.shopid, s.shopname, stt.SizeID, stt.SizeName, ms.SizeCodeSort, m.SizeTypeFlag, m.SizeLength, m.modelid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag and m.SizeLength = '{model.SizeLength}' left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid where c.MaterialID = '{model.MaterialID}' and c.CompanyID = 'KM' and s.shopid =( SELECT s.shopid from ods.ods_erp_SD_Mat_Material2 c left join dw.dw_SKCSizeType sst on c.MaterialID = sst.MaterialID left join dw.dw_SizeTextType stt on sst.SizeTypeCode = stt.SizeTypeCode left join goods.goods_DC_Bas_ModelMinQty m on c.CompanyID = m.CompanyID and stt.SizeID = m.SizeID and c.KindID = m.KindID and c.ModelID = m.ModelID and c.ItemID = m.ItemID and stt.SizeTypeFlag = m.SizeTypeFlag left join dw.dw_Shop s on m.ShopID = s.ShopID left join ods.ods_erp_SD_Mat_Size ms on stt.SizeID = ms.sizeid left join dw.dw_AreaProCity apc on s.AreaID = apc.AreaID and s.CompanyID = apc.CompanyID where c.CompanyID = 'KM' and c.MaterialID = '{model.MaterialID}' and m.MinQty is not null order by s.ShopID LIMIT 1 ) order by m.SizeLength,ms.sizecodesort";
                            dtSelect = Helper.ExecuteDataTable(new VerticaCommand(listSqlCountry, null));
                            listSelect = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtSelect).ToList();
                        }
                        if (listSelect.Count == 0)
                        {
                            InsertToError(dtError, model);
                            index++; continue;
                        }
                        #endregion 取全国其他店铺的本货号最低深度

                        #endregion 获取最低深度尺码列表

                        model.SizeList = listSelect;

                        //计算
                        if (!Compute(model))
                        {
                            InsertToError(dtError, model);
                            index++; continue;
                        }
                        //timeCompute.Stop();
                        //计算完成后,把结果写入dt中
                        foreach (var size in model.SizeList)
                        {
                            DataRow dr = dtNew.NewRow();
                            dr["CompanyID"] = model.CompanyID;
                            dr["ShopID"] = model.ShopID;
                            dr["ShopCode"] = model.ShopCode;
                            dr["MaterialID"] = model.MaterialID;
                            dr["TotalQty"] = model.TotalQty;
                            dr["SizeLength"] = model.SizeLength;
                            dr["SizeID"] = size.SizeID;
                            dr["SizeMinQty"] = size.MinQty;
                            dr["Qty"] = size.SizeQty;
                            dr["MofifyDTM"] = model.MofifyDTM;
                            dr["Checker"] = model.Checker;
                            dtNew.Rows.Add(dr);
                        }
                        #region Delete

                        if (model.ShopID?.Length > 0 &&
                            model.MaterialID.Length > 0)
                        {
                            string deleteSql = $@"Delete from goods.goods_DC_Bas_PLOrder where ShopID='{model.ShopID}' and MaterialID='{model.MaterialID}'";
                            deleteHashSet.Add(deleteSql);
                        }

                        #endregion Delete
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        index++; continue;
                    }
                    //如果数据量大于限制数量
                    if (dt.Rows.Count > limit)
                    {
                        //如果到了限制的行数,开始往数据库提交                        
                        //并清空临时变量
                        if (count >= limit)
                        {
                            #region 拼接删除的sql语句

                            //拼接删除的sql语句
                            var deleteSqlStr = new StringBuilder();
                            foreach (var deleteSql in deleteHashSet)
                            {
                                deleteSqlStr.Append(deleteSql);
                                deleteSqlStr.Append(";");
                            }
                            cmdList.Add(new CommandInfo()
                            {
                                CommandText = deleteSqlStr.ToString()
                            });
                            #endregion 拼接删除的sql语句
                            //然后调用批量插入的方法
                            Helper.ExecuteSqlTran(cmdList, dtNew, "goods.goods_DC_Bas_PLOrder");
                            deleteHashSet.Clear();
                            dtNew.Clear();
                            cmdList.Clear();
                            count = -1;
                        }

                        //如果遍历Excel完毕,还有未提交的数据,则提交最后的数据
                        if (index + 1 == dt.Rows.Count)
                        {
                            if (dtNew.Rows.Count > 0)
                            {
                                #region 拼接删除的sql语句

                                //拼接删除的sql语句
                                var deleteSqlStr = new StringBuilder();
                                foreach (var deleteSql in deleteHashSet)
                                {
                                    deleteSqlStr.Append(deleteSql);
                                    deleteSqlStr.Append(";");
                                }
                                cmdList.Add(new CommandInfo()
                                {
                                    CommandText = deleteSqlStr.ToString()
                                });
                                #endregion 拼接删除的sql语句
                                Helper.ExecuteSqlTran(cmdList, dtNew, "goods.goods_DC_Bas_PLOrder");
                            }
                        }
                    }

                    count++;
                    index++;
                }
                //如果数据量小于等于限制量,则认为导入的数据量较小,不用分批插入
                if (dt.Rows.Count <= limit)
                {
                    #region 拼接删除的sql语句

                    //拼接删除的sql语句
                    var deleteSqlStr = new StringBuilder();
                    foreach (var deleteSql in deleteHashSet)
                    {
                        deleteSqlStr.Append(deleteSql);
                        deleteSqlStr.Append(";");
                    }
                    cmdList.Add(new CommandInfo()
                    {
                        CommandText = deleteSqlStr.ToString()
                    });
                    #endregion 拼接删除的sql语句
                    //然后调用批量插入的方法
                    Helper.ExecuteSqlTran(cmdList, dtNew, "goods.goods_DC_Bas_PLOrder");
                }
                if (dtError.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.IsSuccess = true;
                    result.ExtraInfo = "导入成功";
                    result.Message = $"成功导入{index - dtError.Rows.Count}条数据,错误{dtError.Rows.Count}条";
                    result.Url = ExcelUtil.ExportErrorExeclToDB(dtError, path, dictFields, "下载错误数据", num);
                    num++;
                }
                else
                {
                    result.IsSuccess = true;
                    result.ExtraInfo = "导入成功";
                    result.Message = $"成功导入{index - dtError.Rows.Count}条数据,错误{dtError.Rows.Count}条";
                }

            }
            catch (Exception ex)
            {
                //Debugger.Break();
                //Log.Error(ex);
                Log.Error(ex.StackTrace);
                Log.Error(ex.InnerException.StackTrace);
                if (dtError.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.IsSuccess = true;
                    result.ExtraInfo = "导入失败";
                    result.Message = $"成功导入{index - dtError.Rows.Count}条数据,错误{dtError.Rows.Count}条";
                    result.Url = ExcelUtil.ExportErrorExeclToDB(dtError, path, dictFields, "下载错误数据", num);
                    num++;
                    return result;
                }
            }
            return result;


        }

        public override DataTable GetExportData(string Keys)
        {
            throw new NotImplementedException();
        }

        private bool Compute(PlanningOrderQuantityModelSizeList param)
        {
            bool ComputeResult = false;
            try
            {
                if (param == null || param.SizeList == null || param.SizeList.Count == 0)
                {
                    return ComputeResult;
                }

                //新增和批量导入时,如果skc总量 < 最低深度的合计,则直接把最低深度分给对应的尺码的数量
                if (param.TotalQty < param.SizeList.Sum(p => p.MinQty))
                {
                    foreach (var item in param.SizeList)
                    {
                        item.SizeQty = item.MinQty;
                    }
                    ComputeResult = true;
                    return ComputeResult;
                }
                #region 查询城市码比资料

                IList<PlanningOrderQuantityModelSizeRate> listSizeRate = null;
                //查询是否有缓存,
                //没有就返回
                if (listSizeRateCache == null || listSizeRateCache.Count == 0)
                {
                    ComputeResult = false;
                    return ComputeResult;
                }
                //有的话,先走缓存
                else
                {
                    if (listSizeRateCache.Count > 0 && listSizeRateCache.Any(
                            p => p.ShopCode == param.ShopCode &&
                                     p.SizeTypeFlag == param.SizeList.FirstOrDefault().SizeTypeFlag &&
                                     p.ModelID == param.SizeList.FirstOrDefault().ModelID))
                    {
                        listSizeRate = listSizeRateCache.Where(
                         p => p.ShopCode == param.ShopCode &&
                                     p.SizeTypeFlag == param.SizeList.FirstOrDefault().SizeTypeFlag &&
                                     p.ModelID == param.SizeList.FirstOrDefault().ModelID).Distinct().ToList();
                    }
                }
                //如果有缓存,还是找不到对应的城市码比资料,就写入错误列表,读取下一条
                if (listSizeRate == null || listSizeRate.Count == 0)
                {
                    ComputeResult = false;
                    return ComputeResult;
                }
                //先排除城市码比资料中没有出现在最低深度中的尺码
                IList<PlanningOrderQuantityModelSizeRate> rateList = new List<PlanningOrderQuantityModelSizeRate>();
                foreach (var item in param.SizeList)
                {
                    if (listSizeRate.Any(p => p.SizeID == item.SizeID))
                    {
                        rateList.Add(listSizeRate.First(p => p.SizeID == item.SizeID));
                    }
                }
                if (rateList == null || rateList.Count == 0)
                {
                    ComputeResult = false;
                    return ComputeResult;
                }
                #endregion 查询城市码比资料
                //重算城市码比资料
                ReComputeSizeRateOfCity(rateList);
                //SKC数量－尺码最低深度 ＝剩余skc数量

                var remainSKC = param.TotalQty - param.SizeList.Sum(p => p.MinQty);

                foreach (var item in param.SizeList)
                {
                    //计算出单个尺码的skc数量
                    decimal sizeSkc = 0;
                    var planningOrderQuantityModelSizeRate = rateList.FirstOrDefault(p => p.SizeID == item.SizeID);
                    if (planningOrderQuantityModelSizeRate != null)
                        sizeSkc = remainSKC * planningOrderQuantityModelSizeRate.SizeRate;
                    var roundSizeSkc = MathHelper.Rounding(Convert.ToDouble(sizeSkc), 0);
                    item.SizeQty = Convert.ToInt32(roundSizeSkc);
                }

                #region 判断大小

                var result = param.SizeList.Sum(p => p.SizeQty) + param.SizeList.Sum(p => p.MinQty) + -param.TotalQty;

                var sortedList = param.SizeList.OrderBy(p => p.SizeCodeSort);
                //判断大小
                //结果汇总大于skc总量
                if (result > 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result > 0)
                        {
                            if (item.SizeQty > 0)
                            {
                                item.SizeQty--;
                                result--;
                            }
                        }
                    }
                }
                //结果汇总大于skc总量
                else if (result < 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result < 0)
                        {
                            if (item.SizeQty > 0)
                            {
                                item.SizeQty++;
                                result++;
                            }
                        }
                    }
                }

                #endregion 判断大小

                #region 校验性计算


                //防止出现不加深度之前算出的skc数量都为0的情况
                //这里再次校验性计算一次,不再判断是否为0
                result = param.SizeList.Sum(p => p.SizeQty) + param.SizeList.Sum(p => p.MinQty) + -param.TotalQty;
                //判断大小
                //结果汇总大于skc总量
                if (result > 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result > 0)
                        {
                            item.SizeQty--;
                            result--;
                        }
                    }
                }
                //结果汇总大于skc总量
                else if (result < 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result < 0)
                        {
                            item.SizeQty++;
                            result++;
                        }
                    }
                }


                #endregion 校验性计算

                //最终的skc数量 = 计算出的skc数量 + 尺码对应的最低深度
                foreach (var item in param.SizeList)
                {
                    item.SizeQty += item.MinQty;
                }
                ComputeResult = true;
            }
            catch (Exception e)
            {
                Log.Error(e.Message);
                Log.Error(e.StackTrace);
            }
            return ComputeResult;
        }

        public void ReComputeSizeRateOfCity(IList<PlanningOrderQuantityModelSizeRate> listSizeRate)
        {
            var sum = listSizeRate.Sum(p => p.SizeRate);
            if (sum == 0)
            {
                return;
            }
            foreach (var item in listSizeRate)
            {
                item.SizeRate = item.SizeRate / sum;
            }
        }

        public void InsertToError(DataTable dtError, PlanningOrderQuantityModelSizeList model)
        {
            DataRow dr = dtError.NewRow();
            dr["CompanyID"] = model.CompanyID;
            dr["ShopName"] = model.ShopName;
            dr["ShopCode"] = model.ShopCode;
            dr["MaterialID"] = model.MaterialID;
            dr["TotalQty"] = model.TotalQty;
            dr["SizeLength"] = model.SizeLength;
            dr["Qty"] = model.Qty;
            dr["MofifyDTM"] = model.MofifyDTM;
            dtError.Rows.Add(dr);
        }

        /// <summary>
        /// 获取DataTable中的指定列的数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public string GetStringFromDataTable(DataTable dt, string column)
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataRow item in dt.Rows)
            {
                if (item[column] != null && !string.IsNullOrWhiteSpace(item[column].ToString()))
                {
                    sb.Append($"'{item[column].ToString()}'");
                    sb.Append(",");
                }
            }
            if (sb.Length > 0)
            {
                sb.Remove(sb.Length - 1, 1);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取DataTable中的指定列的数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public Tuple<string, string, string> GetTupleFromDataTable(DataTable dt, string columnShopCode, string columnMaterialId, string columnSizeLength)
        {
            HashSet<string> hsShopCode = new HashSet<string>();
            HashSet<string> hsMaterialId = new HashSet<string>();
            HashSet<string> hsSizeLength = new HashSet<string>();
            StringBuilder sbShopCode = new StringBuilder();
            StringBuilder sbMaterialId = new StringBuilder();
            StringBuilder sbSizeLength = new StringBuilder();
            foreach (DataRow item in dt.Rows)
            {
                if (item[columnShopCode] != null && !string.IsNullOrWhiteSpace(item[columnShopCode].ToString()))
                {
                    hsShopCode.Add($"'{item[columnShopCode].ToString()}'");
                }
                if (item[columnMaterialId] != null && !string.IsNullOrWhiteSpace(item[columnMaterialId].ToString()))
                {
                    hsMaterialId.Add($"'{item[columnMaterialId].ToString()}'");
                }
                if (item[columnSizeLength] != null && !string.IsNullOrWhiteSpace(item[columnSizeLength].ToString()))
                {
                    hsSizeLength.Add($"'{item[columnSizeLength].ToString()}'");
                }
            }
            if (hsShopCode.Count > 0)
            {
                foreach (var item in hsShopCode)
                {
                    sbShopCode.Append(item);
                    sbShopCode.Append(",");
                }
                sbShopCode.Remove(sbShopCode.Length - 1, 1);
            }
            if (hsMaterialId.Count > 0)
            {
                foreach (var item in hsMaterialId)
                {
                    sbMaterialId.Append(item);
                    sbMaterialId.Append(",");
                }
                sbMaterialId.Remove(sbMaterialId.Length - 1, 1);
            }
            if (hsSizeLength.Count > 0)
            {
                foreach (var item in hsSizeLength)
                {
                    sbSizeLength.Append(item);
                    sbSizeLength.Append(",");
                }
                sbSizeLength.Remove(sbSizeLength.Length - 1, 1);
            }
            return Tuple.Create(sbShopCode.ToString(), sbMaterialId.ToString(), sbSizeLength.ToString());
        }

        public List<PlanningOrderQuantityModelSize> ConvertList(List<MinDeepCacheModel> list)
        {
            List<PlanningOrderQuantityModelSize> result = new List<PlanningOrderQuantityModelSize>();

            foreach (var item in list)
            {
                PlanningOrderQuantityModelSize model = new PlanningOrderQuantityModelSize();
                model.MinQty = item.MinQty;
                model.ModelID = item.ModelID;
                model.OldSizeLength = item.OldSizeLength;
                model.SizeCodeSort = item.SizeCodeSort;
                model.SizeID = item.SizeID;
                model.SizeLength = item.SizeLength;
                model.SizeName = item.SizeName;
                model.SizeQty = item.SizeQty;
                model.SizeTypeFlag = item.SizeTypeFlag;
                result.Add(model);
            }
            return result;
        }
    }
}

